
import json
import traceback
import pandas as pd
from tqdm import tqdm
from django.db import IntegrityError, transaction
from control.models import User
from literature.models import *
from basic_component_related_management.models import *
from dataset.models import *
from unit_management.models import *
import glob
import os

def fix_group_component():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    quantity_entity = Quantity.objects.filter(quantity_name_en = "Dimensionless").first()
    # 补充物性240012
    property_group_entity = PropertyGroup.objects.filter(sei_id=25).first()
    property_entity,_ = Property.objects.get_or_create(
            name = "灰成分-碱酸比",
            name_en = "base/acid ratio",
            abbreviation = "AABAR",
            property_group = property_group_entity,
            sei_id = "240012",
            description = "煤灰中碱性组分含量与酸性组分含量之比，即三氧化二铁、氧化钙、氧化镁、氧化钾、氧化钠含量和与二氧化硅、三氧化二铝、二氧化钛含量和之比。",
            quantity = quantity_entity,
            is_single_value = True,
            is_fixed_value = True,
            is_one_variable = True,
            is_two_variable = True,
            is_multi_variable = True,
            is_reaction = True,
            create_person = super_admin_entity,
            modify_person = super_admin_entity
    )
    variable_entity_list1 = PropertyVariable.objects.all()
    property_variable_map = []
    for variable_entity1 in variable_entity_list1:
        property_variable_map.append(
            PropertyPropertyVariableMap(
                property=property_entity,
                property_variable=variable_entity1
            )
        )
    PropertyPropertyVariableMap.objects.bulk_create(
        property_variable_map)
def initial_oil_component_group():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()

    # 专题数据库
    group_name = ["气化用煤数据子库", "天然气数据子库", "原油评价数据子库", "聚合物产品数据子库",
        "物质结构与物性估算数据子库", "相平衡数据子库", "基础物性数据子库", "炼油产品与石油分子子库"]
    
    visible_users = User.objects.filter(user_type="super_admin")
    for name in group_name:
        databank_entity, _ = DataBank.objects.get_or_create(
            name=name,
            zh_name=name,
            creator=super_admin_entity,
            last_editor=super_admin_entity,
            reviewer=super_admin_entity
        )
        databank_entity.visible_users.set(visible_users)

    # 初始化属性分组
    for name in group_name:
        ComponentGroup.objects.get_or_create(
            name=name,
            create_person=super_admin_entity,
            valid_flag=True,
            modify_person=super_admin_entity
        )
    # 初始化炼油产品属性类型
    oil_product_group = ComponentGroup.objects.filter(
            name="炼油产品与石油分子子库").first()
    oilProductType, _ = Attribute.objects.get_or_create(
        name="炼油产品类型",
        name_en="eOilProductType",
        data_type="short_string",
        create_person=super_admin_entity,
        modify_person=super_admin_entity
    )
    oilOrgName, _ = Attribute.objects.get_or_create(
        name="机构名称",
        name_en="sOrgName",
        data_type="short_string",
        create_person=super_admin_entity,
        modify_person=super_admin_entity
    )
    for obj in [oilProductType, oilOrgName]:
        AttributeComGroupMap.objects.get_or_create(
            attribute=obj,
            componentgroup=oil_product_group
        )

    # 原油评价属性类型
    oil_component_group = ComponentGroup.objects.filter(
        name="原油评价数据子库").first()
    oilOrgName = Attribute.objects.filter(name_en = "sOrgName").first()
    attribute_group = [("国家地区", "Country Region", "short_string"), 
                       ("样品编号", "Sample ID","short_string"), 
                       ("评价日期", "Assay Date", "datetime"), 
                       ("馏分类型", "Oil Type", "short_string"), 
                       ("原油分类", "CrudeType", "short_string"),
                       ("沸点温度下限，℃", "Lower Boiling Temperture Value, ℃", "float"), 
                       ("沸点温度上限，℃", "Upper Boiling Temperture Values, ℃", "float")]
    attribute_entity_list = []
    for attribute in attribute_group:
        entity,_ = Attribute.objects.get_or_create(
            name=attribute[0],
            name_en=attribute[1],
            data_type=attribute[2],
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )
        attribute_entity_list.append(entity)
    attribute_entity_list.insert(0, oilOrgName)
    for obj in attribute_entity_list:
        AttributeComGroupMap.objects.get_or_create(
            attribute=obj,
            componentgroup=oil_component_group
        )
    
    # 原油评价属性类型
    coal_component_group = ComponentGroup.objects.filter(
        name="气化用煤数据子库").first()
    countryName = Attribute.objects.filter(name_en = "Country Region").first()
    oilOrgName = Attribute.objects.filter(name_en = "sOrgName").first()
    attribute_group = [("省份（州）", "sProvince", "short_string"), 
                       ("城市", "sCity","short_string"), 
                       ("县", "sTown", "short_string"), 
                       ("矿（厂）", "sCoalMine", "short_string"), 
                       ("煤层", "sCoalSeam", "short_string"),
                       ("样品类型", "eCoalSampleType", "short_string"), 
                       ("煤类", "eCoalType", "short_string")]
    attribute_entity_list = []
    for attribute in attribute_group:
        entity,_ = Attribute.objects.get_or_create(
            name=attribute[0],
            name_en=attribute[1],
            data_type=attribute[2],
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )
        attribute_entity_list.append(entity)
    attribute_entity_list.insert(0, oilOrgName)
    attribute_entity_list.insert(0, countryName)
    # print(attribute_entity_list)
    for obj in attribute_entity_list:
        # print(obj)
        AttributeComGroupMap.objects.get_or_create(
            attribute=obj,
            componentgroup=coal_component_group
        )
def init_oil_product_dataset_content():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    # 初始化数据集
    oil_product_group = ComponentGroup.objects.filter(
        name="炼油产品与石油分子子库").first()

    folder_path = "../applicant_tables/"
    file_paths = glob.glob(os.path.join(
        folder_path, "炼油产品结构化数据集*.xlsx"))
    for index, file in enumerate(file_paths):
        
        print(f"处理{file}")
        sheet_name="1.OilProductPropList"
        df = pd.read_excel(
            file, sheet_name, header=None, skiprows=[0])
        df = df.T
        df.columns = df.iloc[0]
        df = df[2:].reset_index(drop=True)

        prop_dict = {}
        for _, row in df.iterrows():
            prop_dict[row['ePropNameEN']] = row.to_dict()
        del df

        sheet_name = "2.OilProductIDAndPropData"
        df = pd.read_excel(file, sheet_name=sheet_name,
                        header=0, skiprows=[0, 2], dtype=str).fillna("")
        name_dict = dict(zip(df.columns, df.iloc[0]))

        df = df[1:].reset_index(drop=True)
        # 固定参数
        purpose_others_entity = ExpPurpose.objects.filter(
            zh_name="工作的主要目标").first()
        data_present_unknow_entity = DataPresentWay.objects.filter(
            name="Experimental").first()
        databank_entity = DataBank.objects.filter(name="炼油产品与石油分子子库").first()
        oil_component_group = ComponentGroup.objects.filter(
            name="炼油产品与石油分子子库").first()
        oilProductType = Attribute.objects.filter(name="炼油产品类型").first()
        oilOrgName = Attribute.objects.filter(name="机构名称").first()

        # 初始化查询表格
        property_dict = {}
        property_entity_list = Property.objects.filter(valid_flag=True).all()
        for property_entity in property_entity_list:
            property_dict[property_entity.abbreviation] = property_entity
        unit_dict = {}
        unit_entity_list = Unit.objects.filter(valid_flag=True).all()
        for unit_entity in unit_entity_list:
            unit_dict[unit_entity.sei_id] = unit_entity
        variable_dict = {}
        variable_entity_list = PropertyVariable.objects.filter(
            valid_flag=True).all()
        for variable_entity in variable_entity_list:
            variable_dict[variable_entity.symbol] = variable_entity

        # 循环表格
        with transaction.atomic():
            dataset_to_create = []
            for index, row in tqdm(df.iterrows(), total=len(df), desc="Processing oil product data"):
                # 初始化组分/组分类别/属性信息
                # 组分信息
                component_entity, _ = ComponentIdentifier.objects.get_or_create(
                    name=row['sOilProductNameCN'],
                    name_en=row['sOilProductName'],
                    sei_id=row["nOilProductID"],
                    is_mixture=True,
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                # 组分类别和组分绑定
                ComponentIdeComponentGroupMap.objects.get_or_create(
                    componentidentifier=component_entity,
                    componentGroup=oil_component_group
                )
                # 组分属性：炼油产品类型
                ComponentAttributeValue.objects.get_or_create(
                    componentidentifier=component_entity,
                    attribute=oilProductType,
                    attribute_value_char=row["eOilProductType"]
                )
                # 组分属性：机构名称
                ComponentAttributeValue.objects.get_or_create(
                    componentidentifier=component_entity,
                    attribute=oilOrgName,
                    attribute_value_char=row["sOrgName"]
                )

                dataset_group = {}
                uniq_constrain = []
                for col in df.columns:
                    if col.startswith('ePropValue'):
                        prop_name = name_dict[col]

                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里
                        constrain = (property_content["nPCdataUnitID1"], property_content["sVariableSymbol1"], property_content["nConstraintValue1"],
                                     property_content["nPCdataUnitID2"], property_content["sVariableSymbol2"], property_content["nConstraintValue2"],
                                    property_content["sPhaseSymbol"])
                        if constrain not in dataset_group:
                            dataset_group[constrain] = {
                                'properties': [],  # 存储属性名
                            }
                            uniq_constrain.append(constrain)
                        
                        # 添加当前属性到对应分组
                        dataset_group[constrain]['properties'].append(col)
                # for key, value in dataset_group.items():

                #     print(key)
                #     print(value)
                # print(len(dataset_group))
                # break
                for key, value in dataset_group.items():
                    # 初始化数据集
                    dataset_name_zh = f"{row['nOilProductID']}--{row['sOilProductNameCN']}--{key[2]}{key[0]}--{key[5]}{key[3]}"
                    dataset_name_en = f"{row['sOilProductName']}--{key[2]}{key[0]}--{key[5]}{key[3]}"
                    applicant_reference_entity = Literature.objects.filter(
                        liter_code=row['nPCdataCitID']).first() if row['nPCdataCitID'] != "" else None
                    phase_list = []
                    dataset = ExpDataset.objects.create(
                        name=dataset_name_zh,
                        zh_name=dataset_name_en,
                        description="",
                        comment="",
                        source_liter=applicant_reference_entity,
                        purpose=purpose_others_entity,
                        data_present_way=data_present_unknow_entity,
                        review_status=1,
                        databank=databank_entity,
                        creator=super_admin_entity,
                        last_editor=super_admin_entity,
                    )
                    ExpDatasetComponentMap.objects.create(
                        exp_dataset=dataset,
                        component=component_entity
                    )
                    # 处理相态/约束
                    phases_group = []
                    datapoints = []
                    constrains = []
                    datapoint_entity = DataPoint.objects.create()
                    for col in value["properties"]:
                        prop_name = name_dict[col]
                        # print(prop_dict[prop_name])
                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里

                        # 相态
                        if property_content["sPhaseSymbol"] not in phases_group:
                            phases_group.append(property_content["sPhaseSymbol"])
                        # 物性
                        property_map = ExpDatasetPropertyMap.objects.create(
                            exp_dataset=dataset,
                            property=property_dict[property_content["ePropAbbreviation"]],
                            unit=unit_dict[property_content["nPCdataUnitID"]],
                            measurement_error_percent=False,
                        )
                        property_map.prop_component.set([component_entity])
                        # 物性数据点
                        if row[col] != "":
                            datapoint_property_entity = DataPointExpDatasetPropertyMap.objects.create(
                                data_point=datapoint_entity,
                                exp_dataset_property_map=property_map,
                                value=row[col],
                                measurement_error_value = None
                            )
                            datapoints.append(datapoint_entity)
                        # 约束(单位，符号，相态，值)
                        constrain1 = (property_content["nPCdataUnitID1"], property_content["sVariableSymbol1"],
                                    property_content["sPhaseSymbol"], property_content["nConstraintValue1"])
                        constrain2 = (property_content["nPCdataUnitID1"], property_content["sVariableSymbol2"],
                                    property_content["sPhaseSymbol"], property_content["nConstraintValue2"])
                        if constrain1 not in constrains:
                            constrains.append(constrain1)
                        if constrain2 not in constrains:
                            constrains.append(constrain2)
                    dataset.data_points.set(datapoints)
                    phases_set = Phase.objects.filter(abbreviation__in=phases_group)
                    dataset.phases.set(phases_set)

                    # 约束(单位，符号，相态，值)
                    for constrain in constrains:
                        unit_entity = Unit.objects.filter(sei_id = constrain[0]).first()
                        variable_entity = variable_dict[constrain[1]]
                        phase_entity = Phase.objects.filter(abbreviation = constrain[2]).first()
                        constrain_entity = ExpDatasetConstraintMap.objects.create(
                            exp_dataset = dataset,
                            constraint = variable_entity,
                            unit = unit_entity,
                            value = constrain[3],
                            cons_phase = phase_entity
                        )
                        constrain_entity.cons_component.set([component_entity])

def init_oil_component_group():
    with transaction.atomic():
        super_admin_entity = User.objects.filter(user_account="superadmin").first()
        # 初始化数据集
        folder_path = "../applicant_tables/"
        file_paths = glob.glob(os.path.join(
            folder_path, "原油结构化数据集*.xlsx"))
        # 组分属性
        oil_type = Attribute.objects.filter(name="馏分类型").first()
        sample_id = Attribute.objects.filter(name="样品编号").first()
        assay_date = Attribute.objects.filter(name="评价日期").first()
        lower_boiling = Attribute.objects.filter(name="沸点温度下限，℃").first()
        upper_boiling = Attribute.objects.filter(name="沸点温度上限，℃").first()
        country_region = Attribute.objects.filter(name="国家地区").first()
        crudeType = Attribute.objects.filter(name="原油分类").first()
        oilOrgName = Attribute.objects.filter(name="机构名称").first()
        oil_component_group = ComponentGroup.objects.filter(
                name="原油评价数据子库").first()
        percent_unit = Unit.objects.filter(sei_id = "U-0-2").first()

        for index, file in enumerate(file_paths):
            # with transaction.atomic():
            # 读取1.CrudeOilID
            df = pd.read_excel(
                file, sheet_name="1.CrudeOilID", skiprows=[1])
            crude_oil_dict = df.set_index('sCrudeOilID').to_dict(orient='index')

            del df

            # 读取2.Crude-OilCutRelation
            df = pd.read_excel(
                file, sheet_name="2.Crude-OilCutRelation", skiprows=[0, 2])
            crude_oil_relation_dict = df.set_index('sOilCutID').to_dict(orient='index')

            df6 = pd.read_excel(
                file, header = 0, sheet_name="6.CrudeGroup")
            crudeGroup_composition = {}
            for index, row in df6.iterrows():
                component_entity, _ = ComponentIdentifier.objects.get_or_create(
                    name=row['中文名'],
                    name_en=row['英文名'],
                    sei_id=row["ID"],
                    is_mixture=True,
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                crudeGroup_composition[row["ID"]] = component_entity
            del df6
            crudeGroup_composition_value = {}
            df5 = pd.read_excel(
                file, header = 0, skiprows=[1], sheet_name="5.CrudeGroupData")        
            df5 = df5.drop(['nCrudeOilID','sOilCutNameCN','sOilCutName'], axis=1)
            crudeGroup_composition_value = df5.set_index('nOilCutID').to_dict(orient='index')

            # 初始化组分信息/组分属性
            print("初始化组分信息")
            df = df.iloc[::-1].reset_index(drop=True).fillna("")
            # 预定义属性映射表
            ATTRIBUTE_MAPPING = [
                (country_region, "sCountryRegion"),
                (oilOrgName, "sOrgName"),
                (crudeType, "eCrudeType"),
                (sample_id, "sSampleID"),
                (assay_date, "sDataAssayDate"),
                (oil_type, "eOilType", False),  # 最后一个参数表示是否为原油属性
                (lower_boiling, "nLowerBoilingTempValues", False),
                (upper_boiling, "nUpperBoilingTempValues", False)
            ]

            component_constitute = []
            for _, row in tqdm(df.iterrows(), total=len(df), desc="初始化石油评价组分"):
                # 创建或获取组分实体
                component_entity, _ = ComponentIdentifier.objects.get_or_create(
                    name=row['sOilCutNameCN'],
                    name_en=row['sOilCutName'],
                    sei_id=row["sOilCutID"],
                    is_mixture=True,
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                if not row["sOilCutID"].endswith("1"):
                    component_constitute.append(component_entity)
                    filtered_items = {k: v for k, v in crudeGroup_composition_value[row["sOilCutID"]].items() if pd.notna(v)}
                    for key, value in filtered_items.items():
                        constitute_name = key
                        constitute_value = value
                        constitute_entity = crudeGroup_composition[constitute_name]
                        ComponentIdentifierConstituent.objects.get_or_create(
                            componentidentifier = component_entity,
                            constituent = constitute_entity,
                            constituent_ratio = constitute_value,
                            constituent_ratio_unit = percent_unit
                        )
                else:
                    for elem in component_constitute:
                        ComponentIdentifierConstituent.objects.get_or_create(
                            componentidentifier = component_entity,
                            constituent = elem,
                        )
                    component_constitute = []
                # 绑定组分类别
                ComponentIdeComponentGroupMap.objects.get_or_create(
                    componentidentifier=component_entity,
                    componentGroup=oil_component_group
                )
                
                # 批量处理属性
                for item in ATTRIBUTE_MAPPING:
                    attribute, field, is_crude_attr = item if len(item) == 3 else (*item, True)
                    value = None
                    if is_crude_attr:
                        if row["sOilCutID"].endswith("1"):
                            value = crude_oil_dict[row["sCrudeOilID"]].get(field)
                    else:
                        if not row["sOilCutID"].endswith("1"):
                            value = row[field]
                    if value and value != "":
                        ComponentAttributeValue.objects.get_or_create(
                            componentidentifier=component_entity,
                            attribute=attribute,
                            attribute_value_char=value if field not in ["sDataAssayDate", "nLowerBoilingTempValues", "nUpperBoilingTempValues"] else None,
                            attribute_value_float = value if field in ["nLowerBoilingTempValues", "nUpperBoilingTempValues"] else None,
                            attribute_value_datetime=value.strip().replace("/", "-") if field == "sDataAssayDate" else None
                        )
                # if row["sOilCutID"].endswith("1"):
                #     break
            del df

def init_oil_dataset_content():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    # 初始化数据集
    folder_path = "../applicant_tables/"
    file_paths = glob.glob(os.path.join(
        folder_path, "原油结构化数据集*.xlsx"))
    
    for index, file in enumerate(file_paths):
        # 读取3.CrudeOilPropList
        df = pd.read_excel(
            file, sheet_name="3.CrudeOilPropList", header=None, skiprows=[0]).fillna("")
        df = df.T
        df.columns = df.iloc[0]
        df = df[2:].reset_index(drop=True)
        # 初始化物性字典
        prop_dict = {}
        for _, row in df.iterrows():
            prop_dict[row['ePropAbbreviation']] = row.to_dict()
        del df

        df = pd.read_excel(file_paths[0], sheet_name="4.OilCutIDAndPropData",
                        header=0, skiprows=[1,2], dtype=str)
        name_dict = dict(zip(df.columns, df.iloc[0]))
        df = df[1:].fillna("")


        # 固定参数
        purpose_others_entity = ExpPurpose.objects.filter(
            zh_name="工作的主要目标").first()
        data_present_unknow_entity = DataPresentWay.objects.filter(
            name="Experimental").first()
        databank_entity = DataBank.objects.filter(name="原油评价数据子库").first()


        # 初始化查询表格
        property_dict = {}
        property_entity_list = Property.objects.filter(valid_flag=True).all()
        for property_entity in property_entity_list:
            property_dict[property_entity.abbreviation] = property_entity
        unit_dict = {}
        unit_entity_list = Unit.objects.filter(valid_flag=True).all()
        for unit_entity in unit_entity_list:
            unit_dict[unit_entity.sei_id] = unit_entity
        variable_dict = {}
        variable_entity_list = PropertyVariable.objects.filter(
            valid_flag=True).all()
        for variable_entity in variable_entity_list:
            variable_dict[variable_entity.symbol] = variable_entity
        
        # prop_dict 第三张表
        # name_dict第四张表索引

        # 循环表格
        with transaction.atomic():
            dataset_to_create = []
            for index, row in tqdm(df.iterrows(), total=len(df), desc="初始化原油评价数据集"):
                dataset_group = {}
                uniq_constrain = []
                component_entity = ComponentIdentifier.objects.filter(sei_id = row["nOilCutID"]).first()
                for col in df.columns:
                    if col.startswith('ePropValue'):
                        prop_name = name_dict[col]
                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里
                        constrain = (property_content["ConstraintType1"], property_content["sVariableSymbol1"], property_content["nConstraintValue1"],
                                        property_content["ConstraintType2"], property_content["sVariableSymbol2"], property_content["nConstraintValue2"],
                                    property_content["sPhaseSymbol"])
                        if constrain not in dataset_group:
                            dataset_group[constrain] = {
                                'properties': [],  # 存储属性名
                            }
                            uniq_constrain.append(constrain)
                        
                        # 添加当前属性到对应分组
                        dataset_group[constrain]['properties'].append(col)
                # for key, value in dataset_group.items():
                #     print(key)
                #     print(value)
                # break
                for key, value in dataset_group.items():
                    # 初始化数据集
                    dataset_name_zh = f"{row['nOilCutID']}--{row['sOilCutNameCN']}--{key[2]}{key[0]}--{key[5]}{key[3]}"
                    dataset_name_en = f"{row['sOilCutName']}--{key[2]}{key[0]}--{key[5]}{key[3]}"
                    dataset = ExpDataset.objects.create(
                        name=dataset_name_zh,
                        zh_name=dataset_name_en,
                        description="",
                        comment="",
                        purpose=purpose_others_entity,
                        data_present_way=data_present_unknow_entity,
                        review_status=1,
                        databank=databank_entity,
                        creator=super_admin_entity,
                        last_editor=super_admin_entity,
                    )
                    ExpDatasetComponentMap.objects.create(
                        exp_dataset=dataset,
                        component=component_entity
                    )
                    # 处理相态/约束
                    phases_group = []
                    datapoints = []
                    constrains = []
                    datapoint_entity = DataPoint.objects.create()
                    for col in value["properties"]:
                        prop_name =  name_dict[col]
                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里

                        # 相态
                        if property_content["sPhaseSymbol"] not in phases_group and property_content["sPhaseSymbol"] != "":
                            phases_group.append(property_content["sPhaseSymbol"])
                        # 物性
                        if property_content["ePropAbbreviation"] != "" and property_content["nPCdataUnitID"] != "":
                            property_map = ExpDatasetPropertyMap.objects.create(
                                exp_dataset=dataset,
                                property=property_dict[property_content["ePropAbbreviation"]],
                                unit=unit_dict[property_content["nPCdataUnitID"]],
                                measurement_error_percent=False,
                            )
                            property_map.prop_component.set([component_entity])
                            # 物性数据点
                            if row[col] != "":
                                datapoint_property_entity = DataPointExpDatasetPropertyMap.objects.create(
                                    data_point=datapoint_entity,
                                    exp_dataset_property_map=property_map,
                                    value=row[col],
                                    measurement_error_value = None
                                )
                                datapoints.append(datapoint_entity)
                        # 约束(单位，符号，相态，值)
                        constrain1 = (property_content["ConstraintType1"], property_content["sVariableSymbol1"],
                                    property_content["sPhaseSymbol"], property_content["nConstraintValue1"])
                        constrain2 = (property_content["ConstraintType2"], property_content["sVariableSymbol2"],
                                    property_content["sPhaseSymbol"], property_content["nConstraintValue2"])
                        if constrain1 not in constrains:
                            constrains.append(constrain1)
                        if constrain2 not in constrains:
                            constrains.append(constrain2)
                    dataset.data_points.set(datapoints)
                    if len(phases_group) > 0:
                        phases_set = Phase.objects.filter(abbreviation__in=phases_group)
                    dataset.phases.set(phases_set)

                    # 约束(单位，符号，相态，值)
                    for constrain in constrains:
                        if constrain[0] != "" and constrain[1] != "" and constrain[2] != "" and constrain[3] != "":
                            unit_entity = Unit.objects.filter(unit_symbol = constrain[0]).first()
                            variable_entity = variable_dict[constrain[1]]
                            phase_entity = Phase.objects.filter(abbreviation = constrain[2]).first()
                            constrain_entity = ExpDatasetConstraintMap.objects.create(
                                exp_dataset = dataset,
                                constraint = variable_entity,
                                unit = unit_entity,
                                value = constrain[3],
                                cons_phase = phase_entity
                            )
                            constrain_entity.cons_component.set([component_entity])

def init_coal_component_group():
    with transaction.atomic():
        super_admin_entity = User.objects.filter(user_account="superadmin").first()
        # 初始化数据集
        folder_path = "../applicant_tables/"
        file_paths = glob.glob(os.path.join(
            folder_path, "原煤结构化数据集*.xlsx"))
        
        # 组分属性
        countryName = Attribute.objects.filter(name_en="Country Region").first()
        province = Attribute.objects.filter(name_en="sProvince").first()
        city = Attribute.objects.filter(name_en="sCity").first()
        town = Attribute.objects.filter(name_en="sTown").first()
        coalMine = Attribute.objects.filter(name_en="sCoalMine").first()
        coalSeam = Attribute.objects.filter(name_en="sCoalSeam").first()
        coalSampleType = Attribute.objects.filter(name_en="eCoalSampleType").first()
        coalType = Attribute.objects.filter(name_en="eCoalType").first()
        OrgName = Attribute.objects.filter(name_en="sOrgName").first()
        coal_component_group = ComponentGroup.objects.filter(
                name="气化用煤数据子库").first()
        countryName = Attribute.objects.filter(name_en = "Country Region").first()

        percent_unit = Unit.objects.filter(sei_id = "U-0-2").first()

        # 初始化组分构成
        constitutes = ["镜质体", "惰质体", "壳质体", "矿物"]
        component_constitute = []
        for i in range(0,4):
            sub_component_entity, _ = ComponentIdentifier.objects.get_or_create(
                name=constitutes[i],
                is_mixture=True,
                create_person=super_admin_entity,
                modify_person=super_admin_entity
            )
            component_constitute.append((sub_component_entity, i+1))
            # 绑定组分类别
            ComponentIdeComponentGroupMap.objects.get_or_create(
                componentidentifier=sub_component_entity,
                componentGroup=coal_component_group
            )
        for index, file in enumerate(file_paths):
            # with transaction.atomic():
            # 读取1.RCID
            df = pd.read_excel(
                file, sheet_name="1.RCID", skiprows=[0, 2])
            coal_comp_info_dict = df.set_index('nRCID').to_dict(orient='index')

            # 初始化组分信息/组分属性
            print(f"初始化组分信息{file}")
            df = df.reset_index(drop=True).fillna("")

            for index, row in tqdm(df.iterrows(), total=len(df), desc="初始化组分信息"):
                # 创建或获取组分实体
                main_component_entity, _ = ComponentIdentifier.objects.get_or_create(
                    name=row['sRCNameCN'],
                    name_en=row['sRCName'],
                    sei_id=row["nRCID"],
                    is_mixture=True,
                    description = row["sInformation"],
                    create_person=super_admin_entity,
                    modify_person=super_admin_entity
                )
                # 绑定组分类别
                ComponentIdeComponentGroupMap.objects.get_or_create(
                    componentidentifier=main_component_entity,
                    componentGroup=coal_component_group
                )

                # 组分构成
                
                for elem in component_constitute:
                    row[f"nRCComposition{i+1}"]
                    ComponentIdentifierConstituent.objects.get_or_create(
                        componentidentifier = main_component_entity,
                        constituent = elem[0],
                        constituent_ratio = row[f"nRCComposition{elem[1]}"] if row[f"nRCComposition{elem[1]}"] != '' else None,
                        constituent_ratio_unit = percent_unit
                    )
                
                # 预定义属性映射表
                ATTRIBUTE_MAPPING = [
                    (countryName, "sCountryRegion"),
                    (OrgName, "sOrgName"),
                    (province, "sProvince"),
                    (city, "sCity"),
                    (town, "sTown"),
                    (coalMine, "sCoalMine"),
                    (coalSeam, "sCoalSeam"),
                    (coalSampleType, "eCoalSampleType"),
                    (coalType, "eCoalType")
                ]
                # 批量处理属性
                for item in ATTRIBUTE_MAPPING:
                    attribute, field, _ = item if len(item) == 3 else (*item, True)
                    value = row[field]
                    if value:
                        ComponentAttributeValue.objects.get_or_create(
                            componentidentifier=main_component_entity,
                            attribute=attribute,
                            attribute_value_char= value if value != "" else None
                        )
            del df

def init_coal_dataset_content():
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    # 初始化数据集
    folder_path = "../applicant_tables/"
    file_paths = glob.glob(os.path.join(
        folder_path, "原煤结构化数据集*.xlsx"))

    for index, file in enumerate(file_paths):
        # 跳过测试
        if index == 0:
            continue
        # 读取2.RCPropList
        df = pd.read_excel(
            file, sheet_name="2.RCPropList", header=0, skiprows=[0,2]).fillna("")
        # 初始化物性字典
        prop_dict = {}
        for _, row in df.iterrows():
            prop_dict[row['ePropAbbreviation']] = row.to_dict()
        del df

        df = pd.read_excel(file, sheet_name="3.RCPropData ",
                        header=0, skiprows=[0,2,4], dtype=str).fillna("")
        name_dict = dict(zip(df.columns, df.iloc[0]))
        df = df[1:]
        # 固定参数
        purpose_others_entity = ExpPurpose.objects.filter(
            zh_name="工作的主要目标").first()
        data_present_exp_entity = DataPresentWay.objects.filter(
            name="Experimental").first()
        data_present_calculate_entity = DataPresentWay.objects.filter(
            name="Calculated").first()
        databank_entity = DataBank.objects.filter(name="气化用煤数据子库").first()


        # 初始化查询表格
        property_dict = {}
        property_entity_list = Property.objects.filter(valid_flag=True).all()
        for property_entity in property_entity_list:
            property_dict[property_entity.abbreviation] = property_entity
        unit_dict = {}
        unit_entity_list = Unit.objects.filter(valid_flag=True).all()
        for unit_entity in unit_entity_list:
            unit_dict[unit_entity.sei_id] = unit_entity
        variable_dict = {}
        variable_entity_list = PropertyVariable.objects.filter(
            valid_flag=True).all()
        for variable_entity in variable_entity_list:
            variable_dict[variable_entity.symbol] = variable_entity
        
        # prop_dict 第2张表
        # name_dict第3张表索引
        with transaction.atomic():
            excel_file = pd.ExcelFile(file)
            if "4.SpecialRCPropData" in excel_file.sheet_names:
                print(file)
                dataset_group = {}
                df4 = pd.read_excel(
                    file, sheet_name="4.SpecialRCPropData", header=0, skiprows=[0,2]).fillna("")
                for index, row in df4.iterrows():
                    if row["nRCID"] not in dataset_group:
                        dataset_group[row["nRCID"]] = []
                        dataset_group[row["nRCID"]].append(row.to_dict())
                    else:
                        dataset_group[row["nRCID"]].append(row.to_dict())
                for key, value in dataset_group.items(): # 组RC-0229
                    print(key)
                    component_entity = ComponentIdentifier.objects.filter(sei_id = key).first()
                    phases_group = []
                    datapoints = []
                    constrains = []
                    dataset_entity = None
                    variable_entity = None
                    property_map = None
                    for index, sub_dict in enumerate(value): # 一行
                        datapoint_entity = DataPoint.objects.create()
                        datapoints.append(datapoint_entity)
                        if index == 0:
                            liter_entity = Literature.objects.filter(liter_code=sub_dict["nPCdataCitID"]).first()
                            dataset_entity = ExpDataset.objects.create(
                                zh_name=sub_dict["ePropName"],
                                name=sub_dict["ePropNameCN"],
                                description="",
                                comment="",
                                purpose=purpose_others_entity,
                                data_present_way=data_present_exp_entity,
                                review_status=1,
                                source_liter = liter_entity,
                                databank=databank_entity,
                                creator=super_admin_entity,
                                last_editor=super_admin_entity,
                            )
                            ExpDatasetComponentMap.objects.create(
                                exp_dataset=dataset_entity,
                                component=component_entity
                            )
                            # 变量
                            variable_var = variable_dict[sub_dict[f"sVariableSymbol"]]
                            unit_var = Unit.objects.filter(unit_symbol=sub_dict["sVariableUnit"]).first()
                            phase_var = Phase.objects.filter(abbreviation=sub_dict[f"sVariablePhase"]).first()
                            print("变量单位：", unit_var.sei_id)
                            print("变量值:", sub_dict["sVariableValue"])
                            variable_entity = ExpDatasetVariableMap.objects.create(
                                exp_dataset = dataset_entity,
                                variable = variable_var,
                                unit = unit_var,
                                var_phase = phase_var,
                                measurement_error_percent=False
                            )
                            # 物性
                            prop_unit =  unit_dict[sub_dict["nPCdataUnitID"]]
                            print("物性单位：", prop_unit.sei_id, sub_dict["nPCdataUnitID"])
                            print("物性值：", sub_dict["nPropValue"])
                            property_phase = Phase.objects.filter(abbreviation = sub_dict["ePhaseSymbolLsit"]).first()
                            property_map = ExpDatasetPropertyMap.objects.create(
                                exp_dataset=dataset_entity,
                                property=property_dict[sub_dict["ePropAbbreviation"]],
                                unit=prop_unit,
                                measurement_error_percent=True,
                                prop_phase = property_phase
                            )
                            property_map.prop_component.set([component_entity])
                        
                        datapoint_variable_entity1 = DataPointExpDatasetVariableMap.objects.create(
                            data_point=datapoint_entity,
                            exp_dataset_variable_map=variable_entity,
                            value=sub_dict["sVariableValue"]
                        )

                        # 相态列表
                        if sub_dict["ePhaseSymbolLsit"] not in phases_group:
                            phases_group.append(sub_dict["ePhaseSymbolLsit"])

                        datapoint_property_entity = DataPointExpDatasetPropertyMap.objects.create(
                            data_point=datapoint_entity,
                            exp_dataset_property_map=property_map,
                            value=sub_dict["nPropValue"]
                        )
                        
                        # 约束
                        print(constrains)
                        if sub_dict["ConstraintID"] not in constrains:
                            constrains.append(sub_dict["ConstraintID"])
                            for i in range(1,3):
                                constraint_temp = variable_dict[sub_dict[f"sConstrainSymbol{i}"]]
                                unit_temp = Unit.objects.filter(unit_symbol=sub_dict[f"sConstrainUnit{i}"]).first()
                                constrain = ExpDatasetConstraintMap.objects.create(
                                    exp_dataset = dataset_entity,
                                    constraint = constraint_temp,
                                    unit = unit_temp,
                                    value = sub_dict[f"sConstrainValue{i}"]
                                )
                                constrain.cons_component.set([component_entity])
                    if len(phases_group) > 0:
                        phases_set = Phase.objects.filter(abbreviation__in=phases_group)
                    dataset_entity.phases.set(phases_set)
                    dataset_entity.data_points.set(datapoints)
        return
        # 循环表格
        with transaction.atomic():
            dataset_to_create = []
            for index, row in tqdm(df.iterrows(), total=len(df), desc="气化用煤数据子库"):
                
                dataset_group = {}
                uniq_property_group = []
                component_entity = ComponentIdentifier.objects.filter(sei_id = row["nRCID"]).first()
                # step1：将物性进行分组
                for col in df.columns:
                    if col.startswith('ePropValue'):
                        prop_name = name_dict[col]
                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里
                        property_group = property_dict[property_content["ePropAbbreviation"]].property_group
                        if property_group not in dataset_group:
                            dataset_group[property_group] = {
                                'properties': [],  # 存储属性名
                            }
                            uniq_property_group.append(property_group)
                        
                        # 添加当前属性到对应分组
                        dataset_group[property_group]['properties'].append(col)
                # for key, value in dataset_group.items():
                #     print(key)
                #     print(value)
                # break
                # step2：初始化数据集
                for key, value in dataset_group.items():
                    # 初始化数据集
                    dataset_name_zh = f"{row['nRCID']}--{row['nSampleID']}--{key}"
                    dataset = ExpDataset.objects.create(
                        name=dataset_name_zh,
                        zh_name=dataset_name_zh,
                        description="",
                        comment="",
                        purpose=purpose_others_entity,
                        data_present_way=data_present_exp_entity,
                        review_status=1,
                        databank=databank_entity,
                        creator=super_admin_entity,
                        last_editor=super_admin_entity,
                    )
                    ExpDatasetComponentMap.objects.create(
                        exp_dataset=dataset,
                        component=component_entity
                    )
                    # 处理相态/约束
                    phases_group = []
                    constrains = []
                    datapoint_entity = DataPoint.objects.create()
                    for col in value["properties"]:
                        prop_name = name_dict[col]
                        try:
                            property_content = prop_dict[prop_name]
                            # 如果存在，继续处理
                        except KeyError:
                            print(f"字典中没有 {prop_name}，跳过")
                            continue  # 如果在循环里

                        # 相态
                        if property_content["ePhaseSymbolList"] not in phases_group and property_content["ePhaseSymbolList"] != "":
                            phases_group.append(property_content["ePhaseSymbolList"])
                        # 物性
                        if property_content["ePropAbbreviation"] != "" and property_content["nPCdataUnitID"] != "":
                            property_map = ExpDatasetPropertyMap.objects.create(
                                exp_dataset=dataset,
                                property=property_dict[property_content["ePropAbbreviation"]],
                                unit=unit_dict[property_content["nPCdataUnitID"]],
                                measurement_error_percent=False,
                            )
                            property_map.prop_component.set([component_entity])
                            # 物性数据点
                            if row[col] != "":
                                datapoint_property_entity = DataPointExpDatasetPropertyMap.objects.create(
                                    data_point=datapoint_entity,
                                    exp_dataset_property_map=property_map,
                                    value=row[col],
                                    measurement_error_value = None
                                )
                        # 约束(单位，符号，相态，值)
                        constrain1 = (property_content["ConstraintType1"], property_content["sVariableSymbol1"],
                                    property_content["ePhaseSymbolList"], property_content["nConstraintValue1"])
                        constrain2 = (property_content["ConstraintType2"], property_content["sVariableSymbol2"],
                                    property_content["ePhaseSymbolList"], property_content["nConstraintValue2"])
                        if constrain1 not in constrains:
                            constrains.append(constrain1)
                        if constrain2 not in constrains:
                            constrains.append(constrain2)
                    dataset.data_points.set([datapoint_entity])
                    if len(phases_group) > 0:
                        phases_set = Phase.objects.filter(abbreviation__in=phases_group)
                    dataset.phases.set(phases_set)

                    # 约束(单位，符号，相态，值)
                    for constrain in constrains:
                        if constrain[0] != "" and constrain[1] != "" and constrain[2] != "" and constrain[3] != "":
                            unit_entity = Unit.objects.filter(unit_symbol = constrain[0]).first()
                            variable_entity = variable_dict[constrain[1]]
                            phase_entity = Phase.objects.filter(abbreviation = constrain[2]).first()
                            constrain_entity = ExpDatasetConstraintMap.objects.create(
                                exp_dataset = dataset,
                                constraint = variable_entity,
                                unit = unit_entity,
                                value = constrain[3],
                                cons_phase = phase_entity
                            )
                            constrain_entity.cons_component.set([component_entity])

def init_applicant_dataset_content():
    # fix_group_component()
    # initial_oil_component_group()
    # init_oil_product_dataset_content()
    # init_oil_component_group()
    # init_oil_dataset_content()
    # init_coal_component_group()
    init_coal_dataset_content()